package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.SaleDAO;
import com.fly.estore.domain.MemberInfo;
import com.fly.estore.domain.MemberLevel;
import com.fly.estore.domain.SaleInfo;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/28.
 */
@Repository("SaleDAO")
public class SaleDAOImpl extends BaseDAO<SaleInfo> implements SaleDAO {

    public int addSaleInfo(final SaleInfo saleInfo) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("insert into sale_info(sale_id, member_id, pay_price, total_price, status, cutrate, remark, create_id, create_time)")
                .append("values(?, ?, ?, ?, ?, ?, ?, ?, ?)");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, saleInfo.getSaleId());
                ps.setString(i++, saleInfo.getMember().getMemberId());
                ps.setFloat(i++, saleInfo.getPayPrice());
                ps.setFloat(i++, saleInfo.getTotalPrice());
                ps.setString(i++, saleInfo.getStatus());
                ps.setFloat(i++, saleInfo.getCutrate());
                ps.setString(i++, saleInfo.getRemark());
                ps.setString(i++, saleInfo.getCreateId());
                ps.setString(i++, saleInfo.getCreateTime());
            }
        });
        return update;
    }

    public int updateSaleInfo(final SaleInfo saleInfo) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("update sale_info set ")
                .append(" member_id = ?,")
                .append(" pay_price = ?,")
                .append(" total_price = ?,")
                .append(" status = ?,")
                .append(" cutrate = ?,")
                .append(" remark = ?,")
                .append(" last_update_id = ?,")
                .append(" last_update_time = ?")
                .append(" where sale_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, saleInfo.getMember().getMemberId());
                ps.setFloat(i++, saleInfo.getPayPrice());
                ps.setFloat(i++, saleInfo.getTotalPrice());
                ps.setString(i++, saleInfo.getStatus());
                ps.setFloat(i++, saleInfo.getCutrate());
                ps.setString(i++, saleInfo.getRemark());
                ps.setString(i++, saleInfo.getLastUpdateId());
                ps.setString(i++, saleInfo.getLastUpdateTime());
                ps.setString(i++, saleInfo.getSaleId());
            }
        });
        return update;
    }

    public int deleteById(final String saleId) {
        int update = 0;
        String sql = "delete from sale_info where sale_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, saleId);
            }
        });
        return update;
    }

    public SaleInfo getById(String saleId) {
        SaleInfo sale = new SaleInfo();
        StringBuilder sql = new StringBuilder();
        sql.append("select s.sale_id, s.pay_price, s.total_price, s.status, s.cutrate, s.remark, s.create_id, s.create_time, s.last_update_id, s.last_update_time,")
                .append(" m.member_id, m.member_no, m.member_name,")
                .append(" ml.member_level_id, ml.level_name, ml.discount")
                .append(" from sale_info s")
                .append(" left join member_info m on s.member_id = m.member_id")
                .append(" left join member_level ml on m.level = ml.member_level_id")
                .append(" where s.sale_id = ?");
        List<SaleInfo> resultList = getJdbcTemplate().query(sql.toString(), new SaleRowMapper(), new Object[]{saleId});
        if (!CollectionUtils.isEmpty(resultList)){
            sale = resultList.get(0);
        }
        return sale;
    }

    public List<SaleInfo> getList() {
        List<SaleInfo> saleList = new ArrayList();
        StringBuilder sql = new StringBuilder();
        sql.append("select s.sale_id, s.pay_price, s.total_price, s.status, s.cutrate, s.remark, s.create_id, s.create_time, s.last_update_id, s.last_update_time,")
                .append(" m.member_id, m.member_no, m.member_name,")
                .append(" ml.member_level_id, ml.level_name, ml.discount")
                .append(" from sale_info s")
                .append(" left join member_info m on s.member_id = m.member_id")
                .append(" left join member_level ml on m.level = ml.member_level_id")
                .append(" order by s.create_time desc");
        saleList = getJdbcTemplate().query(sql.toString(), new SaleRowMapper());
        return saleList;
    }

    public List<SaleInfo> getListByMemberId(String memberId) {
        List<SaleInfo> saleList = new ArrayList<SaleInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("select s.sale_id, s.pay_price, s.total_price, s.status, s.cutrate, s.remark, s.create_id, s.create_time, s.last_update_id, s.last_update_time,")
                .append(" m.member_id, m.member_no, m.member_name,")
                .append(" ml.member_level_id, ml.level_name, ml.discount")
                .append(" from sale_info s")
                .append(" left join member_info m on s.member_id = m.member_id")
                .append(" left join member_level ml on m.level = ml.member_level_id")
                .append(" where s.member_id = ?")
                .append(" order by s.create_time desc");
        saleList = getJdbcTemplate().query(sql.toString(), new SaleRowMapper(), new Object[]{memberId});
        return saleList;
    }

    public float getSalePrice(String date) {
        StringBuilder sql = new StringBuilder();
        sql.append("select ifnull(sum(pay_price), 0) from sale_info where status = ? and create_time > ?");
        float totalPrice = getJdbcTemplate().queryForObject(sql.toString(), new Object[]{SaleInfo.STATUS_CONFIRMED, date}, Float.class);
        return totalPrice;
    }

    public List querySalePrice(String date){
        StringBuilder sql = new StringBuilder();
        sql.append("select ifnull(sum(pay_price, 0), year(create_time), month(create_time), day(create_time)")
                .append(" from sale_info")
                .append(" where status = ?")
                .append(" and create_time > ?")
                .append(" group by year(create_time), month(create_time), day(create_time)")
                .append(" order by year(create_time), month(create_time), day(create_time)");
        List result = getJdbcTemplate().queryForList(sql.toString(), new Object[]{SaleInfo.STATUS_CONFIRMED, date});
        return result;
    }

    public class SaleRowMapper implements RowMapper<SaleInfo> {
        public SaleInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            SaleInfo sale = new SaleInfo();
            sale.setSaleId(rs.getString("sale_id"));
            sale.setPayPrice(rs.getFloat("pay_price"));
            sale.setTotalPrice(rs.getFloat("total_price"));
            sale.setStatus(rs.getString("status"));
            sale.setCutrate(rs.getFloat("cutrate"));
            sale.setRemark(rs.getString("remark"));
            MemberInfo memberInfo = new MemberInfo();
            memberInfo.setMemberId(rs.getString("member_id"));
            memberInfo.setMemberName(rs.getString("member_name"));
            memberInfo.setMemberNo(rs.getString("member_no"));
            MemberLevel level = new MemberLevel();
            level.setLevelName(rs.getString("level_name"));
            level.setDiscount(rs.getFloat("discount"));
            memberInfo.setMemberLevel(level);
            sale.setMember(memberInfo);
            sale.setCreateId(rs.getString("create_id"));
            sale.setCreateTime(rs.getString("create_time"));
            sale.setLastUpdateId(rs.getString("last_update_id"));
            sale.setLastUpdateTime(rs.getString("last_update_time"));
            return sale;
        }
    }
}
